using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Collections.Specialized;
using System.ComponentModel;
using System.Data;
using System.Linq;
using System.Reflection;

using System.Data.SQLite;
using DbType = System.Data.DbType;
namespace MvvmOrm.Models
{
    /// <summary>
    /// Specifies an operator for a Param object of a database
    /// item included in this assembly.
    /// </summary>
    public enum DbOperator
    {
        EQUAL = 0,
        NOT_EQUAL,
        GREATER_OR_EQUAL,
        GREATER,
        LESS_OR_EQUAL,
        LESS,
        LIKE,
        NOT_LIKE,
        IS_NULL,
        IS_NOT_NULL,
        IN,
        NOT_IN
    }


    /// <summary>Wraps a connection to the database</summary>
    /// <remarks>
    /// It can be declare alone, and used across different Operation class
    /// instance of different database items by setting their DbConnection property
    /// to it. This allows the use of transactions with more than one database item at a time.
    /// </remarks>
    public partial class DbConnection : IDisposable
    {

        #region DBMS specifics
        /// <summary>Gets or sets the database name</summary>
        public string Database { get; set; }

        /// <summary>Sets the password</summary>
        public string Password { private get; set; }

        /// <summary>Inititalizes a new instance of the DbConnection class</summary>
        public DbConnection()
        {
            Database = "";
            Password = "";
            Initalize();
        }            

        /// <summary>Inititalizes a new instance of the DbConnection class</summary>
        /// <param name="db">The database</param>
        /// <param name="pass">The password</param>
        public DbConnection(string db, string pass)
        {
            Database = db;
            Password = pass;
            Initalize();
        }            

        /// <summary>Inititalizes a new instance of the DbConnection class</summary>
        /// <param name="db">The database</param>
        public DbConnection(string db)
        {
            Database = db;
            Password = "";
            Initalize();
        }
            
        /// <summary>Opens the connection to the database</summary>
        public void Open()
        {
            if (string.IsNullOrEmpty(Password))
                _connection.ConnectionString = "Data Source=" + Database + ";";
            else
                _connection.ConnectionString = "Data Source=" + Database + ";" + "Password=" + Password + ";";
            _connection.Open();
        }

        /// <summary>Clone the DbConnection</summary>
        /// <returns>The cloned DbConnection</returns>
        public DbConnection Clone()
        {
            DbConnection conn = new DbConnection();
            conn.Database = Database;
            conn.Password = Password;
            conn.AutoConnect = _autoConnect;
            conn.CommandTimeout = _commandTimeout;
            return conn;
        }

        #endregion

        bool _autoConnect;
        int _commandTimeout;
        bool _autoConnected;

        internal SQLiteConnection _connection;
        SQLiteTransaction _transaction;

        /// <summary>Gets or sets if the connection should auto-connect.</summary>
        /// <remarks>
        /// If AutoConnect is true, the connection doesn't have to be open
        /// to interact with the database. The connection will then open and
        /// close automatically.
        /// If AutoConnect is true, but the DbConnection is manually opened,
        /// then the connection will not automatically close after a database
        /// operation occurs. It will remain open.
        /// </remarks>
        public bool AutoConnect
        {
            get { return _autoConnect; }
            set { _autoConnect = value; }
        }
      
        /// <summary>
        /// Gets or sets the timeout for each executed data command.
        /// </summary>
        public int CommandTimeout
        {
            get { return _commandTimeout; }
            set { _commandTimeout = value; }
        }
            
        /// <summary>Gets the current state of the connection</summary>
        public ConnectionState ConnectionState
        {
            get { return _connection.State; }
        }

        public void Dispose()
        {
            if (_connection != null)
                _connection.Dispose();
        }

        void Initalize()
        {
            _autoConnect = true;
            _autoConnected = false;
            _commandTimeout = 30;
            _connection = new SQLiteConnection ();
        }
            
        /// <summary>
        /// Closes the connection to the database.
        /// </summary>
        public void Close()
        {
            _connection.Close();
        }

        /// <summary>
        /// Begins a database transaction.
        /// </summary>
        /// <exception cref="System.InvalidOperationException">
        /// Parallel transactions are not supported.
        /// </exception>
        public void BeginTransaction()
        {
            ManageAutoConnect();
            _transaction = _connection.BeginTransaction();
        }

        /// <summary>
        /// Begins a database transaction with the current System.Data.IsolationLevel value.
        /// </summary>
        /// <param name="isolationLevel">The transaction isolation level for this connection.</param>
        /// <exception cref="System.InvalidOperationException">
        /// Parallel transactions are not supported.
        /// </exception>
        public void BeginTransaction(IsolationLevel isolationLevel)
        {
            ManageAutoConnect();
            _transaction = _connection.BeginTransaction(isolationLevel);
        }

        /// <summary>
        /// Commits the database transaction.
        /// </summary>
        /// <exception cref="System.Exception">
        /// An error occurred while trying to commit the transaction.
        /// </exception>
        /// <exception cref="System.InvalidOperationException">
        /// The transaction has already been committed or rolled back.
        /// -or- The connection is broken.
        /// </exception>
        public void CommitTransaction()
        {
            _transaction.Commit();
            _transaction = null;
            ManageAutoDisconnect();
        }  

        /// <summary>
        /// Rolls back a transaction from a pending state.
        /// </summary>
        /// <exception cref="System.Exception">
        /// An error occurred while trying to commit the transaction.
        /// </exception>
        /// <exception cref="System.InvalidOperationException">
        /// The transaction has already been committed or rolled back.
        /// -or- The connection is broken.
        /// </exception>
        public void RollbackTransaction()
        {
            _transaction.Rollback();
            _transaction = null;
            ManageAutoDisconnect();
        }

        /// <summary>Executes a command</summary>
        /// <param name="command">The command to execute.</param>
        /// <returns>Number of rows affected</returns>
        public int ExecuteNonQuery(string sql)
        {
            using (var command = new SQLiteCommand(sql))
                return ExecuteNonQuery(command);
        }    

        /// <summary>Executes a command</summary>
        /// <param name="command">The command to execute.</param>
        /// <returns>Number of rows affected</returns>
        public int ExecuteNonQuery(SQLiteCommand command)
        {
            int ret;
            ManageAutoConnect();
            command.Connection = _connection;
            command.CommandTimeout = _commandTimeout;
            
            if (_transaction != null)
                command.Transaction = _transaction;
            ret = command.ExecuteNonQuery();
            ManageAutoDisconnect();
            return ret;
        }
            
        /// <summary>Executes a list of commands</summary>
        /// <param name="commands">The commands to execute.</param>
        /// <returns>Total number of rows affected</returns>
        public int ExecuteNonQuery(IEnumerable<SQLiteCommand> commands)
        {
            int ret = 0;
            ManageAutoConnect();
            foreach (SQLiteCommand command in commands)
                ret += ExecuteNonQuery(command);
            ManageAutoDisconnect();
            return ret;
        } 

        /// <summary>Executes a command</summary>
        /// <param name="command">The command to execute.</param>
        /// <returns>First value of the first record in the result set</returns>
        public object ExecuteScalar(string sql)
        {
            using (var command = new SQLiteCommand(sql))
                return ExecuteScalar(command);
        }    

        /// <summary>Executes a command</summary>
        /// <param name="command">The command to execute.</param>
        /// <returns>First value of the first record in the result set</returns>
        public object ExecuteScalar(SQLiteCommand command)
        {
            object ret;
            ManageAutoConnect();
            command.Connection = _connection;
            command.CommandTimeout = _commandTimeout;
            
            if (_transaction != null)
                command.Transaction = _transaction;
            ret = command.ExecuteScalar();
            ManageAutoDisconnect();
            return ret;
        }   

        public SQLiteDataReader ExecuteReader(string sql)
        {
            using (var command = new SQLiteCommand(sql))
                return ExecuteReader(command);
        }   

        /// <summary>
        /// Executes a SQLiteCommand against this instance of DbConnection
        /// and returns an SQLiteDataReader of the resulting set of rows.
        /// </summary>
        /// <param name="command">The Command to execute</param>
        /// <returns>The DataReader</returns>
        /// <remarks>
        /// This method requires the connection to be already opened, even if
        /// AutoConnect is set to true
        /// </remarks>
        public SQLiteDataReader ExecuteReader(SQLiteCommand command)
        {
            command.Connection = _connection;
            command.CommandTimeout = _commandTimeout;
            
            if (_transaction != null)
                command.Transaction = _transaction;
            return command.ExecuteReader();
        }
            
        internal void ManageAutoConnect()
        {
    	    if (_autoConnect  && _connection.State != ConnectionState.Open) {
    		    Open();
    		    _autoConnected = true;
    	    }
        }
            
        internal void ManageAutoDisconnect()
        {
    	    if (_autoConnected && _transaction == null
                && _connection.State == ConnectionState.Open)
                Close();
            if (_transaction != null && _transaction.Connection != null)
                return;
    	    _autoConnected = false;
        }
    }
}
